import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * Database is a class that specifies the interface to the movie database. Uses
 * JDBC and the MySQL Connector/J driver.
 */
public class Database {
	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}
	
//	srv016.dmz.hbg.lu.se
//	db14
//	mNZdrk8gKZ

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://srv016.dmz.hbg.lu.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			System.err.println(e);
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			System.err.println(e);
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
		}
		conn = null;
		System.out.println("Connection to database lost");
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public boolean createCookie(String cookieName) {
		String getIngredientsSQL = "select * from ingredientqty where cookieName='"
				+ cookieName + "'";
		ArrayList<Ingredient> ingredientList = new ArrayList<Ingredient>();
		Ingredient ingredient;

		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getIngredientsSQL);
			while (rs.next()) {
				ingredient = new Ingredient(rs.getString("ingredientName"),
						rs.getInt("ingredientQty"));
				ingredientList.add(ingredient);
			}
			String updateIngredientSQL;
			for (int i = 0; i < ingredientList.size(); i++) {
				updateIngredientSQL = "update ingredients set storageAmount = storageAmount - "
						+ ingredientList.get(i).getQuantity()
						+ " where ingredientName = "
						+ ingredientList.get(i).getName();
				stmt.executeUpdate(updateIngredientSQL);
			}
			stmt.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean addOrder(String customerName, String deliveryDate) {
		String addOrderSQL = "insert into orders (customerName, deliveryDate, delivered) values('"
				+ customerName + "', '" + deliveryDate + "', " + "false)";
		Statement stmt;
		boolean b;
		try {
			stmt = conn.createStatement();
			b = stmt.execute(addOrderSQL);
		} catch (SQLException e) {
			e.printStackTrace();
			b = false;
		}
		return b;
	}

	public boolean addPallet(int oID, String cookieName) {
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
		String date = dateFormat.format(cal.getTime());
		String time = timeFormat.format(cal.getTime());
		System.out.println(date);
		System.out.println(time);
		String addPalletSQL = "insert into pallets (cookieName, bakingDate, bakingTime, oID) values('" + cookieName + "', '" + date + "', '" + time + "', " + oID + ")";
		try {
			Statement stmt = conn.createStatement();
			if(stmt.execute(addPalletSQL)){
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public ArrayList<Customer> getCustomers() {
		ArrayList<Customer> customerList = new ArrayList<Customer>();
		Customer customer;
		String getCustomersSQL = "select * from Customers";
		String getOrdersSQL;
		try {
			Statement stmt1 = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			ResultSet rs = stmt1.executeQuery(getCustomersSQL);
			while (rs.next()) {
				getOrdersSQL = "select count(*) from Orders where customerName='"
						+ rs.getString("customerName") + "'";
				ResultSet orders = stmt2.executeQuery(getOrdersSQL);
				while (orders.next()) {
					customer = new Customer(rs.getString("customerName"),
							rs.getString("address"), orders.getInt(1));
					customerList.add(customer);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customerList;
	}

	public ArrayList<Pallet> getPallets() {
		ArrayList<Pallet> palletList = new ArrayList<Pallet>();
		Pallet pallet;
		String getPalletsSQL = "select * from Pallets";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getPalletsSQL);
			while (rs.next()) {
				pallet = new Pallet(rs.getInt("pID"),
						rs.getString("cookieName"), rs.getBoolean("blocked"),
						rs.getString("bakingDate"), rs.getString("bakingTime"), rs.getInt("oID"),
						rs.getString("location"));
				palletList.add(pallet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return palletList;
	}

	public boolean blockPallet(int pID) {
		boolean blocked = false;
		String blockPalletSQL = "update pallets set blocked = 1 where pID = "
				+ pID;
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(blockPalletSQL);
			blocked = true;
		} catch (SQLException e) {
			e.printStackTrace();
			blocked = false;
		}
		return blocked;
	}

	public boolean unblockPallet(int pID) {
		boolean blocked = false;
		String unblockPalletSQL = "update pallets set blocked = 0 where pID = "
				+ pID;
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(unblockPalletSQL);
			blocked = true;
		} catch (SQLException e) {
			e.printStackTrace();
			blocked = false;
		}
		return blocked;
	}

	public String[] getPalletInfo(int oID) {
		String getPalletInfoSQL = "select * from orders where oID=" + oID;
		String[] temp = new String[3];
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getPalletInfoSQL);

			while (rs.next()) {
				temp[0] = rs.getString("deliveryDate");
				temp[1] = rs.getBoolean("delivered") ? "Yes" : "No";
				temp[2] = rs.getString("customerName");
			}
			if (temp[2] == null) {
				return new String[] { "Unknown", "Unknown", "Unknown" };
			} else {
				return temp;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return new String[] { "Unknown", "Unknown", "Unknown" };
		}
	
	}
	public ArrayList<Pallet> search(String cookieName, String date1, String date2, int pID, int oID, boolean blocked, String customer){
		ArrayList<Pallet> palletList = new ArrayList<Pallet>();
		String searchSQL = "select * from pallets natural join orders where ";
		if(!cookieName.equals("")){
			searchSQL += "cookieName = '" + cookieName + "' and ";
		}
		if(!date1.equals("") && !date2.equals("")){
			searchSQL += "bakingDate between '" + date1 + "' and '" + date2 + "' and ";
		} else if(!date1.equals("")) {
			searchSQL += "bakingDate = '" + date1 + "' and "; 
		} else if(!date2.equals("")) {
			searchSQL += "bakingDate = '" + date2 + "' and ";
		}
		if(pID != 0){
			searchSQL += "pID = " + pID + " and ";
		}
		if(oID != 0){
			searchSQL += "oID = " + oID + " and ";
		}
		if(!customer.equals("")){
			searchSQL += "customerName = '" + customer + "' and ";
		}
		searchSQL += "blocked = " + blocked; 
		System.out.println(searchSQL);
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(searchSQL);
			while(rs.next()){
				palletList.add(new Pallet(rs.getInt("pID"),
						rs.getString("cookieName"), rs.getBoolean("blocked"),
						rs.getString("bakingDate"), rs.getString("bakingTime"), rs.getInt("oID"),
						rs.getString("location")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return palletList;
	}
	public String getCustomer(int oID){
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select customerName from orders where oID = " + oID);
			while(rs.next()){
				return rs.getString("customerName");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
}
